package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.DeviceShiftRelation;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/8/31.
 */
@Service
public class PartnerDeviceShiftRelationDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String databaseTag = "{database}";
    private static final String isExistFromTo = "SELECT *\n" +
            "    FROM\n" +
            "        `" + databaseTag + "`.device_shift_relation\n" +
            "    WHERE ((begin_date <= ? AND end_date >= ?)\n" +
            "            OR (begin_date <= ? AND end_date >= ?)\n" +
            "            OR (begin_date >= ? AND end_date <= ?))\n" +
            "\t\tAND plan_state = 1 ORDER BY begin_date";
    private static final String insertInto="INSERT INTO `"+databaseTag+"`.`device_shift_relation` (`shift_name`, `start_time`, `end_time`, `user_id`, `plan_state`, `begin_date`, `end_date`, `shift_lead`) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
    private static final String deleteQuery="DELETE FROM `"+databaseTag+"`.`device_shift_relation` WHERE `start_time`=? and`plan_state`=? and`begin_date`=?;";
    private static final String getShiftListByDeviceId= "SELECT \n" +
            "    start_time, end_time, begin_date, end_date\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.device_shift_relation\n" +
            "WHERE\n" +
            "    plan_state = 1\n" +
            "        AND ((begin_date <= DATE(NOW())\n" +
            "        AND end_date >= DATE(NOW()))\n" +
            "        OR (begin_date > DATE(NOW()))) order by begin_date,start_time;";
    private static final String getAllByBeginDate="select * from `"+databaseTag+"`.device_shift_relation where begin_date = date(?) and plan_state=1;";
    private static final String getAllByEndDate="select * from `"+databaseTag+"`.device_shift_relation where end_date = date(?) and plan_state=1;";
    private static final String getShiftByDay = "SELECT * FROM `"+databaseTag+"`.device_shift_relation where date(?) between begin_date and end_date order by `start_time`;";

    public List<Map<String, Object>> getExist(String database, Date startTime, Date endTime) {

        return jdbcTemplate.queryForList(isExistFromTo.replace(databaseTag, database), startTime, startTime, endTime,
                endTime, startTime, endTime);
    }

    public boolean isExist(String database, String startTime, String endTime) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList(isExistFromTo.replace(databaseTag, database), startTime, startTime, endTime, endTime, startTime, endTime);

        return list.size() > 0;
    }

    public void save(String database,DeviceShiftRelation deviceShiftRelation){
        jdbcTemplate.update(insertInto.replace(databaseTag, database), pss -> {
                    pss.setString(1,deviceShiftRelation.getShiftName());
                    pss.setString(2,deviceShiftRelation.getStartTime());
                    pss.setString(3, deviceShiftRelation.getEndTime());
                    pss.setString(4, deviceShiftRelation.getUserId());
                    pss.setInt(5, deviceShiftRelation.getPlan());
                    pss.setDate(6,  new java.sql.Date(deviceShiftRelation.getBeginDate().getTime()));
                    pss.setDate(7, new java.sql.Date(deviceShiftRelation.getEndDate().getTime()));
                    pss.setString(8,deviceShiftRelation.getShiftLead());
                }
        );
    }

    public void delete(String database,String startTime,int planState,Date beginDate){
        jdbcTemplate.update(deleteQuery.replace(databaseTag,database),startTime,planState,beginDate);
    }

    public List<Map<String,Object>> getShiftList(String database){
        return jdbcTemplate.queryForList(getShiftListByDeviceId.replace(databaseTag,database));
    }

    public List<Map<String,Object>> getShiftListByBeginDate(String database,Date beginDate){
        return jdbcTemplate.queryForList(getAllByBeginDate.replace(databaseTag,database),beginDate);
    }

    public List<Map<String,Object>> getShiftListByEndDate(String database,Date beginDate){
        return jdbcTemplate.queryForList(getAllByEndDate.replace(databaseTag,database),beginDate);
    }

    public List<Map<String,Object>> getShiftListByDay(String database,String day){
        return jdbcTemplate.queryForList(getShiftByDay.replace(databaseTag,database),day);
    }
}
